package com.itcast.util;

import com.itcast.bihuan.CatchLinks;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.io.*;
import org.apache.commons.io.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicLong;

/**
 * 将文本文件的内容导入到excel中
 * 
 * @author root
 */
public class ExcelMerge2One {
	final static Logger logger = LoggerFactory.getLogger(ExcelMerge2One.class);
	static int rows = 65536;
	static String excelDefaultName = "招投标";
	final static AtomicLong total = new AtomicLong(0);
	
	public static void main(String[] args) {
		File baseDir = new File("e:\\zuizhong");
		File targetDir = new File("C:\\Users\\Administrator\\Desktop\\ni");
		File tmp = new File("e:\\zuizhong1");
////		merge(baseDir);
////		File baseFile=new File(Configure.BASE_DIR+File.separator+Configure.N_RESULT+File.separator+Configure.N_FILTER);
////		File targetFile=new File(Configure.BASE_DIR+File.separator+Configure.N_RESULT+File.separator+Configure.N_EXCEL);
//		writeTo("e:\\data\\result\\filter","e:\\zuizhong");
		try {
			merge(targetDir,tmp,"");
		} catch (IOException e) {
			e.printStackTrace();
		}
		//获取玩这个文件后对他进行读取然后输出
//		List<List<Object>> lists = ExcelUtils.readExcelAll("e:\\zuizhong1\\招投标.xls", 0);
//		int i =0;
//		for(List<Object> lis : lists){
//			i++;
//			System.out.println(i+"--------------------"+lis);
//		}
		System.out.println("当前批次记录总量："+total.intValue());
	}

	public static void writeTo(String baseDir,String targetDir){
		File f = new File(baseDir);
		File targetDirf = new File(targetDir);
		File[] fs = f.listFiles();
		int i = 0;
		try {
			for (File file : fs) {
				if (file.isFile()) {
				}else{
					merge(file,targetDirf,i+"");
				}
				i++;
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static void mergeByString(String baseDirf,String targetDirf,String excelName) throws IOException {
		File baseDir = new File(baseDirf);
		File targetDir = new File(targetDirf);
		if(StringUtils.isEmpty(excelName)){
			excelName=excelDefaultName;
		}
		File excelFile=null;

		if ( baseDir.getName().endsWith(".xls")) {

			WritableWorkbook wwb;
				try {
					Tools.checkAndMakeDirecotry(targetDir);
					excelFile = new File(targetDir.getPath() + File.separator + excelName + ".xls");
					if(!(excelFile.exists())){
						wwb = Workbook.createWorkbook(excelFile);
					}else{
						wwb = Workbook.createWorkbook(excelFile);
					}
					outexByFile(baseDir, wwb);
				} catch (IOException e) {
					e.printStackTrace();
				}
		}
	}
	public static void merge(File baseDir,File targetDir,String excelName) throws IOException {
		if(StringUtils.isEmpty(excelName)){
			excelName=excelDefaultName;
		}
		File excelFile=null;
		List<File> excels=new ArrayList();
		if (baseDir.exists()) {
			for (File file : baseDir.listFiles()) {
				if (file.isFile() && file.getName().endsWith(".xls")) {
					excels.add(file);
				}
			}
			WritableWorkbook wwb;
			if(excels.size()>0){
				try {
					Tools.checkAndMakeDirecotry(targetDir);
					excelFile = new File(targetDir.getPath() + File.separator + excelName + ".xls");
					wwb = Workbook.createWorkbook(excelFile);
					outex(excels, wwb);
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}


	public static void  seeFile (String path) throws IOException {
		File f = new File(path);
		File[] fs = f.listFiles();
		if (fs == null) {
			return;
		}
		String paths = "";
		for (File file : fs) {
			if (file.isFile()) {
				//mergeByString(file.getName(),targetDir,excelName);
				//System.out.println(file.getName());
			} else {
				System.out.println(file.getPath());
				seeFile(file.getPath());
				//seeFile(file.getPath(),targetDir,excelName);
			}
		}
	}

	public static void outexByFile(File file, WritableWorkbook wwb) {
		int rowNum = 0;
		int colNum = 0;
		int sheetNo = 1;
		WritableSheet ws = wwb.createSheet("Sheet1", sheetNo++);
		Label lab = null;
		String temp = null;
		jxl.write.Number nlab = null;
		try {
			initTitle(ws, colNum, rowNum);
			rowNum = 1;
			logger.info(file.getPath());
			Workbook input = Workbook.getWorkbook(file);
			Sheet allSheet[] = input.getSheets();// 得到工作表数组
			for (Sheet sheet : allSheet) {
				for (int i = 1; i < sheet.getRows(); i++) {
					Cell cells[] = sheet.getRow(i);
					colNum = 0;
					for (Cell cell : cells) {
//						if (colNum == 0) {
//							if(StringUtils.isNotBlank(cell.getContents())){
//								nlab = new jxl.write.Number(colNum++, rowNum, Integer.parseInt(cell.getContents()));
//								ws.addCell(nlab);
//							} else {
//								nlab = new jxl.write.Number(colNum++, rowNum, rowNum);
//								ws.addCell(nlab);
//							}
//						} else {
							lab = new Label(colNum++, rowNum, cell.getContents());
							ws.addCell(lab);
//						}
					}
					rowNum++;
					total.incrementAndGet();
					if (rowNum >= rows) {
						ws = wwb.createSheet("Sheet " + (++sheetNo), sheetNo);
						initTitle(ws, colNum, rowNum);
						rowNum = 1;
					}
				}
			}
			input.close();

			logger.info("合并后的总行数：" + rowNum);
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			logger.error(Tools.exception2string(e));

		}
	}

	public static void outex(List<File> files, WritableWorkbook wwb) {
		int rowNum = 0;
		int colNum = 0;
		int sheetNo = 1;
		WritableSheet ws = wwb.createSheet("Sheet1", sheetNo++);
		Label lab = null;
		String temp = null;
		jxl.write.Number nlab = null;
		try {
			initTitle(ws, colNum, rowNum);
			rowNum = 1;
			for (File file : files) {
				logger.info(file.getPath());
				Workbook input = Workbook.getWorkbook(file);
				Sheet allSheet[] = input.getSheets();// 得到工作表数组
				for (Sheet sheet : allSheet) {
					for (int i = 1; i < sheet.getRows(); i++) {
						Cell cells[] = sheet.getRow(i);
						colNum = 0;
						for (Cell cell : cells) {
							if (colNum == 0) {
//								if(StringUtils.isNotBlank(cell.getContents())){
//									nlab = new jxl.write.Number(colNum++, rowNum, Integer.parseInt(cell.getContents()));
//									ws.addCell(nlab);
//								} else {
									nlab = new jxl.write.Number(colNum++, rowNum, rowNum);
									ws.addCell(nlab);
//								}
							} else {
								lab = new Label(colNum++, rowNum, cell.getContents());
								ws.addCell(lab);
							}
						}
						rowNum++;
						total.incrementAndGet();
						if (rowNum >= rows) {
							ws = wwb.createSheet("Sheet " + (++sheetNo), sheetNo);
							initTitle(ws, colNum, rowNum);
							rowNum = 1;
						}
					}
				}
				input.close();
			}
			logger.info("合并后的总行数：" + rowNum);
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			logger.error(Tools.exception2string(e));
			
		}
	}
	
	public static void initTitle(WritableSheet ws, int colNum, int rowNum) throws RowsExceededException, WriteException {
		Label l = new Label(colNum++, rowNum, "顺序号");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "区域");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "抓取时间");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "链接");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "标题");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "匹配关键字");
		ws.addCell(l);
		l = new Label(colNum++, rowNum, "内容");
		ws.addCell(l);
	}
}
